unit libDB_CustomD;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, libMaintenance_CustomD, DB, ADODB;

type
  TdmDB_Custom = class(TdmMaintenance_Custom)
    dsDynamic: TADODataSet;
    dsDetails: TADODataSet;
    dsClone: TADODataSet;
    cmdExecuteSQL: TADOCommand;
  private
    procedure SetFieldProperty(aDataSet: TDataSet);
    procedure RegisterLog(sFunctionCode, sKeyValue, sOperaction, sReason: string);
  protected
    function GetDateFormat: String;
    function GetQtyFormat: String;
    function GetCostFormat: String;
    function GetAmountFormat: String;
    
    procedure BeforeOpenDataSet(aDataSet: TDataSet);virtual;
    procedure AfterOpenDataSet(aDataSet: TDataSet);virtual;

  public
    FKeyValue: string;
    procedure Open;
    procedure Cancel;
    procedure Post;
    procedure Delete(sFunctionCode, sKeyValue, sReason: string);
    function HavPendingUpdate: boolean; overload;
    function HavPendingUpdate(DataSet: TAdoDataSet): Boolean; overload;
    procedure OpenRecord(aKeyValue: String);
  end;

implementation

uses
  libPublicInterfaceU,libTypesU, libBasic_CustomF, libParamsU;
{$R *.dfm}

{ TdmDB_Custom }

{------------------------------------------------------------------------------}
procedure TdmDB_Custom.AfterOpenDataSet(aDataSet: TDataSet);
begin
  SetFieldProperty(aDataSet);
end;

{------------------------------------------------------------------------------}
procedure TdmDB_Custom.BeforeOpenDataSet(aDataSet: TDataSet);
begin

end;

{------------------------------------------------------------------------------}
procedure TdmDB_Custom.Cancel;
var
  i: Integer;
  aDataSet_: TAdoDataSet;
begin
  for I := 0 to lstDataSet.Count - 1 do
  begin
    aDataSet_ := lstDataSet.Items[i];
    if HavPendingUpdate(aDataSet_) then
      aDataSet_.CancelUpdates;
  end;
end;

{------------------------------------------------------------------------------}
procedure TdmDB_Custom.Delete(sFunctionCode, sKeyValue, sReason: string);
begin
  doDisableAllDataSet(True);
  try
    dsMaster.Connection.BeginTrans;
    try
      dsDetails.First;
      while not dsDetails.Eof do
        dsDetails.Delete;
      dsDetails.UpdateBatch();
      dsMaster.Delete;
      dsMaster.UpdateBatch();
      RegisterLog(sFunctionCode, sKeyValue, 'D', sReason);
      dsMaster.Connection.CommitTrans;
    except
      on E: Exception do
      begin
        dsMaster.Connection.RollbackTrans;
        raise Exception.Create(E.Message);
      end;
    end;
  finally
    doDisableAllDataSet(False);
  end;
end;

{------------------------------------------------------------------------------}
function TdmDB_Custom.GetAmountFormat: String;
begin
  Result := '';
  dsDynamic.Close;
  dsDynamic.CommandText := 'select fldAmountFormat = dbo.fn_GetAmountDecimal()';
  dsDynamic.Open;

  if dsDynamic.RecordCount > 0 then
    Result := dsDynamic.FieldByName('fldAmountFormat').AsString;

  dsDynamic.Close;
end;

{------------------------------------------------------------------------------}
function TdmDB_Custom.GetCostFormat: String;
begin
  Result := '';
  dsDynamic.Close;
  dsDynamic.CommandText := 'select fldCostFormat = dbo.fn_GetUnitCostDecimal()';
  dsDynamic.Open;

  if dsDynamic.RecordCount > 0 then
    Result := dsDynamic.FieldByName('fldCostFormat').AsString;

  dsDynamic.Close;
end;

{------------------------------------------------------------------------------}
function TdmDB_Custom.GetDateFormat: String;
begin
  Result := '';
  dsDynamic.Close;
  dsDynamic.CommandText := 'select fldDateFormat from tblSystemParameters';
  dsDynamic.Open;

  if dsDynamic.RecordCount > 0 then
    Result := dsDynamic.FieldByName('fldDateFormat').AsString;

  dsDynamic.Close;
end;

{------------------------------------------------------------------------------}
function TdmDB_Custom.GetQtyFormat: String;
begin
  Result := '';
  dsDynamic.Close;
  dsDynamic.CommandText := 'select fldQtyFormat = dbo.fn_GetQuantityDecimal()';
  dsDynamic.Open;

  if dsDynamic.RecordCount > 0 then
    Result := dsDynamic.FieldByName('fldQtyFormat').AsString;

  dsDynamic.Close;
end;

{------------------------------------------------------------------------------}
function TdmDB_Custom.HavPendingUpdate: boolean;
var
  iLoop: Integer;
begin
  Result := False;
  for iLoop := 0 to lstDataSet.Count - 1 do
  begin
    Result := HavPendingUpdate(TAdoDataSet(lstDataSet[iLoop]));
    if Result then
      exit;
  end;
end;

{------------------------------------------------------------------------------}
function TdmDB_Custom.HavPendingUpdate(DataSet: TAdoDataSet): Boolean;
begin
  Result := False;
  try
    dsClone.Close;
    dsClone.Filter := '';
    dsClone.Filtered := False;
    dsClone.Clone(DataSet);
    dsClone.FilterGroup := fgPendingRecords;
    dsClone.Filtered := True;
    Result := not (dsClone.Eof and dsClone.Bof);
    dsClone.Close;
  Except
    dsClone.Close;
    dsClone.Filter := '';
    dsClone.Filtered := False;
  end;
end;

{------------------------------------------------------------------------------}
procedure TdmDB_Custom.Open;
var
  i: Integer;
  aDataSet_: TAdoDataSet;
begin
  for I := 0 to lstDataSet.Count - 1 do
  begin
    aDataSet_ := lstDataSet.Items[i];
    BeforeOpenDataSet(aDataSet_);
    aDataSet_.Filter := '';
    aDataSet_.Filtered := False;
    aDataSet_.Open;
    AfterOpenDataSet(aDataSet_);
  end;
end;

{------------------------------------------------------------------------------}
procedure TdmDB_Custom.OpenRecord(aKeyValue: String);
var
  iCount: Integer;
  aDataSet_: TADODataSet;
begin
  lstDataSetCommandText.Clear;
  for iCount := 0 to lstDataset.Count - 1 do
  begin
    aDataSet_ := lstDataset.Items[iCount];
    BeforeOpenDataSet(aDataSet_);
    lstDataSetCommandText.Add(aDataSet_.CommandText);
    aDataSet_.Filter := '';
    aDataSet_.Filtered := False;
    if fldKeyFieldName <> '' then
      aDataSet_.Parameters[0].Value := aKeyValue;
//    aDataSet_.AutoCalcFields := True;
    aDataSet_.Open;
    AfterOpenDataSet(aDataSet_);
//    aDataSet_.AutoCalcFields := False;
  end;
end;

{------------------------------------------------------------------------------}
procedure TdmDB_Custom.Post;
var
  i: Integer;
  aDataSet_: TAdoDataSet;
begin
  for I := 0 to lstDataSet.Count - 1 do
  begin
    aDataSet_ := lstDataSet.Items[i];
    if HavPendingUpdate(aDataSet_) then
      if aDataSet_.State in [dsInsert, dsEdit] then
        aDataSet_.Post;
  end;
end;

procedure TdmDB_Custom.RegisterLog(sFunctionCode, sKeyValue,
  sOperaction, sReason: string);
var
  sSQL: string;
begin
  sSQL := 'insert into tblUserLog(fldUserCode, fldFunctionCode, fldAction, fldKeyFieldValue, fldRemarks) '
    + ' Values(%s, %s, %s, %s, %s)';
  cmdExecuteSQL.CommandText :=
    Format(sSQL, [QuotedStr(sUserCode), QuotedStr(sFunctionCode), QuotedStr(sOperaction), QuotedStr(sKeyValue), QuotedStr(sReason)]);
  cmdExecuteSQL.Execute;
end;

{------------------------------------------------------------------------------}
procedure TdmDB_Custom.SetFieldProperty(aDataSet: TDataSet);
var
  i: Integer;
begin
  if not Assigned(aDataSet) then exit;
  if aDataSet.State = dsInActive then exit;

  for I := 0 to aDataSet.FieldCount - 1 do
  begin
    aDataSet.Fields[i].DisplayLabel :=
      PublicFunction.sGetLanguage(Copy(aDataSet.Fields[i].FieldName,4,MaxInt));
    if aDataSet.Fields[i].DataType in [ftDate,ftTime,ftDateTime] then
      TDateTimeField(aDataSet.Fields[i]).DisplayFormat := GetDateFormat;
    if (aDataSet.Fields[i].DataType in [ftFloat, ftCurrency, ftBCD]) then
    begin
      if pos('cost',aDataSet.Fields[i].FieldName) > 0 then
        TFloatField(aDataSet.Fields[i]).DisplayFormat := GetCostFormat;
      if pos('Amount',aDataSet.Fields[i].FieldName) > 0 then
        TFloatField(aDataSet.Fields[i]).DisplayFormat := GetAmountFormat;
      if pos('Qty',aDataSet.Fields[i].FieldName) > 0 then
        TFloatField(aDataSet.Fields[i]).DisplayFormat := GetQtyFormat;
      if pos('Price',aDataSet.Fields[i].FieldName) > 0 then
        TFloatField(aDataSet.Fields[i]).DisplayFormat := GetCostFormat;
    end;
  end;
end;

end.
